package jc.malpractice.service;

import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONObject;
import jc.malpractice.db.DbUtil;
import jc.malpractice.db.Help;

import java.sql.*;

public class MyeventSerice {
    //查询病人信息
    public static JSONObject selectPatient(String in_his_no) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event_patient" +
                            " where in_his_no=? ");
            preparedStatement.setString(1, in_his_no);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result7Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {

            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "查找失败");

        return jo;
    }

    //我的上报事件
    public static JSONObject list(String name){
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }

            preparedStatement = conn.prepareStatement(
                    "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=?");
            preparedStatement.setString(1,name);
            resultSet = preparedStatement.executeQuery();
            System.out.println(resultSet);
            JSONObject r = Help.Result2Json(resultSet);
            preparedStatement.close();
            r.set("code",0);
            return r;
        }catch(Exception ex){
            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    //我的上报事件基本信息
    public static JSONObject selectRough(String name,String event_code){
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }

            preparedStatement = conn.prepareStatement(
                    "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=? and event_all.event_code = ?" +
                            "");
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,event_code);
            resultSet = preparedStatement.executeQuery();
            System.out.println(resultSet);

            // 获取列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 遍历ResultSet中的每条数据
            JSONArray array = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObj = new JSONObject();

                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {

                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(columnName);
                    jsonObj.set(columnName,value);
                }

                //查询粗略信息
                Connection con = DbUtil.getConn();
                PreparedStatement pstmt = con.prepareStatement("select property_en_name,detailed_data from event_property,event_detailed where event_property.property_code = event_detailed.property_code and form_code = ?");
                pstmt.setString(1,jsonObj.getStr("form_code"));
                ResultSet rs = pstmt.executeQuery();
                // 获取列数
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int colCount = rsMetaData.getColumnCount();
                while (rs.next()) {
                    // 遍历每一列
                    System.out.println("名字"+rs.getObject("property_en_name")+"\t数据"+rs.getObject("detailed_data"));
                    String colname = rs.getString(1);
                    jsonObj.set(colname,rs.getObject("detailed_data"));
                    System.out.println("子查询");
                }
                //关闭资源
                rs.close();
                pstmt.close();
                con.close();

                System.out.println("查询");

                jsonObj.set("event_time",resultSet.getString("event_time"));
                array.add(jsonObj);
            }
            JSONObject result = new JSONObject();
            result.set("data",array);

            resultSet.close();
            preparedStatement.close();
            result.set("code",0);
            return result;
        }catch(Exception ex){
            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }

    //根据部门查看事件
    public static JSONObject selectDeptData(String reporter_dept){
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }
            if(reporter_dept.equals("医务科"))
            {
                preparedStatement = conn.prepareStatement(
                        "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and med_dept_status = ? and event_status=?");
                preparedStatement.setString(1,"0");
                preparedStatement.setString(2,"2");
            }
            else {
                preparedStatement = conn.prepareStatement(
                        "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_dept=? and dept_status = ? and event_status=?");
                preparedStatement.setString(1,reporter_dept);
                preparedStatement.setString(2,"0");
                preparedStatement.setString(3,"2");
            } //System.out.println("SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id "+sql_follow);
            resultSet = preparedStatement.executeQuery();
            System.out.println(resultSet);
            JSONObject r = Help.Result2Json(resultSet);
            preparedStatement.close();
            r.set("code",0);
            return r;
        }catch(Exception ex){
            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //条件查询
    public static JSONObject partselect(String name,String event_code,String status,String reporter_dept,String date,String reporter) {
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }

            preparedStatement = conn.prepareStatement(
                    "SELECT * FROM event_all,event_kind,status_table,reporter where event_all.event_code = event_kind.kind_code and event_status = status_id and event_all.reporter_code=reporter.reporter_code and event_all.reporter_code=? and event_all.event_code=?"+
                            " and event_status like '%"+status+"%' and dept_name like '%"+reporter_dept+"%' and event_time like '%"+date+"%' and reporter_name  like '%"+reporter+"%'");
            preparedStatement.setString(1,name);
            preparedStatement.setString(2,event_code);
            resultSet = preparedStatement.executeQuery();
            // 获取列数
            ResultSetMetaData metaData = resultSet.getMetaData();
            int columnCount = metaData.getColumnCount();

            // 遍历ResultSet中的每条数据
            JSONArray array = new JSONArray();
            while (resultSet.next()) {
                JSONObject jsonObj = new JSONObject();

                // 遍历每一列
                for (int i = 1; i <= columnCount; i++) {

                    String columnName = metaData.getColumnLabel(i);
                    Object value = resultSet.getObject(columnName);
                    jsonObj.set(columnName,value);
                }

                //查询粗略信息
                Connection con = DbUtil.getConn();
                PreparedStatement pstmt = con.prepareStatement("select property_en_name,detailed_data from event_property,event_detailed where event_property.property_code = event_detailed.property_code and form_code = ?");
                pstmt.setString(1,jsonObj.getStr("form_code"));
                ResultSet rs = pstmt.executeQuery();
                // 获取列数
                ResultSetMetaData rsMetaData = rs.getMetaData();
                int colCount = rsMetaData.getColumnCount();
                while (rs.next()) {
                    // 遍历每一列
                    System.out.println("名字"+rs.getObject("property_en_name")+"\t数据"+rs.getObject("detailed_data"));
                    String colname = rs.getString(1);
                    jsonObj.set(colname,rs.getObject("detailed_data"));
                    System.out.println("子查询");
                }
                //关闭资源
                rs.close();
                pstmt.close();
                con.close();

                System.out.println("查询");

                jsonObj.set("event_time",resultSet.getString("event_time"));
                array.add(jsonObj);
            }
            JSONObject result = new JSONObject();
            result.set("data",array);

            resultSet.close();
            preparedStatement.close();
            result.set("code",0);
            return result;
        }
        catch (SQLException ex){

            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //条件查询
//    public static JSONObject select(String name,String status,String kind_name){
//        Connection conn = DbUtil.getConn();
//        PreparedStatement preparedStatement = null;
//        ResultSet resultSet = null;
//        try {
//            if (conn == null && conn.isClosed()) {
//                return null;
//            }
//if (status.equals("无")&kind_name.equals("无"))
//{
//    preparedStatement = conn.prepareStatement(
//            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=?");
//    preparedStatement.setString(1,name);
//}
//else if (status.equals("无")){
//    preparedStatement = conn.prepareStatement(
//            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=? and kind_name=?");
//    preparedStatement.setString(1, name);
//    preparedStatement.setString(2,kind_name);
//}
//else if(kind_name.equals("无")){
//    preparedStatement = conn.prepareStatement(
//            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=? and event_status=?");
//    preparedStatement.setString(1, name);
//    preparedStatement.setString(2,status);
//}
//else {
//    preparedStatement = conn.prepareStatement(
//            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_code=?  and kind_name=? and event_status=? ");
//    preparedStatement.setString(1, name);
//    preparedStatement.setString(2,kind_name);
//    preparedStatement.setString(3,status);
//}
//            resultSet = preparedStatement.executeQuery();
//            System.out.println(resultSet);
//            JSONObject r = Help.Result2Json(resultSet);
//            preparedStatement.close();
//            r.set("code",0);
//            return r;
//        }catch(Exception ex){
//            return null;
//        }
//        finally {
//            try {
//                if (resultSet != null) resultSet.close();
//                if (preparedStatement != null) preparedStatement.close();
//                conn.close();
//            } catch (SQLException e) {
//                System.out.println(e.getMessage());
//            }
//        }
//    }
    //处理页面的查询
    public static JSONObject handleSelect(String name,String status,String kind_name){
        Connection conn = DbUtil.getConn();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            if (conn == null && conn.isClosed()) {
                return null;
            }
            if(name.equals("医务科")){
                if (status.equals("无") & kind_name.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id  and event_status=? and med_dept_status=?");
                    preparedStatement.setString(1, "2");
                    preparedStatement.setString(2, "0");

                } else if (status.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and kind_name=? and event_status=? and med_dept_status=?");
                    preparedStatement.setString(1, kind_name);
                    preparedStatement.setString(2, "2");
                    preparedStatement.setString(3, "0");
                } else if (kind_name.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and event_status=? and event_status=? and med_dept_status=?");
                    preparedStatement.setString(1, status);
                    preparedStatement.setString(2, "2");
                    preparedStatement.setString(3, "0");
                } else {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id  and kind_name=? and event_status=? and event_status=? and med_dept_status=?");
                    preparedStatement.setString(1, kind_name);
                    preparedStatement.setString(2, status);
                    preparedStatement.setString(3, "2");
                    preparedStatement.setString(4, "0");
                }
            }
            else {
                if (status.equals("无") & kind_name.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_dept=? and event_status=? and dept_status=?");
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, "2");
                    preparedStatement.setString(3, "0");

                } else if (status.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_dept=? and kind_name=? and event_status=? and dept_status=?");
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, kind_name);
                    preparedStatement.setString(3, "2");
                    preparedStatement.setString(4, "0");
                } else if (kind_name.equals("无")) {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_dept=? and event_status=? and event_status=? and dept_status=?");
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, status);
                    preparedStatement.setString(3, "2");
                    preparedStatement.setString(4, "0");
                } else {
                    preparedStatement = conn.prepareStatement(
                            "SELECT * FROM event_all,event_kind,status_table where event_all.event_code = event_kind.kind_code and event_status = status_id and reporter_dept=?  and kind_name=? and event_status=? and event_status=? and dept_status=?");
                    preparedStatement.setString(1, name);
                    preparedStatement.setString(2, kind_name);
                    preparedStatement.setString(3, status);
                    preparedStatement.setString(4, "2");
                    preparedStatement.setString(5, "0");
                }
            }
            resultSet = preparedStatement.executeQuery();
            System.out.println(resultSet);
            JSONObject r = Help.Result2Json(resultSet);
            preparedStatement.close();
            r.set("code",0);
            return r;
        }catch(Exception ex){
            return null;
        }
        finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }
    }
    //form_code查询所有属性
    public static JSONObject look(String form_code) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event_property,event_detailed where event_property.property_code = event_detailed.property_code and form_code = ?");

            preparedStatement.setString(1, form_code);
            resultSet = preparedStatement.executeQuery();

            jo = Help.Result6Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {
                //conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }


        return jo;
    }

    //查询所有属性
    public static JSONObject selectAllProperty() {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {

            preparedStatement = conn.prepareStatement(
                    "select * from event_property ");

            resultSet = preparedStatement.executeQuery();

            jo = Help.Result6Json(resultSet);
            return jo;
        } catch (SQLException e) {
            try {
                //conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }


        return jo;
    }

    //提交不良事件总表
    public static JSONObject insertEventAll(String form_code,String reporter_code,String reporter_dept,String handler_code,String event_code,String event_time,String event_status) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("insert into event_all(form_code,reporter_code,reporter_dept,handler_code,event_code,event_time,event_status,dept_status,med_dept_status) values(?,?,?,?,?,?,?,?,?)");
            preparedStatement.setString(1, form_code);
            preparedStatement.setString(2, reporter_code);
            preparedStatement.setString(3, reporter_dept);
            preparedStatement.setString(4, handler_code);
            preparedStatement.setString(5, event_code);
            preparedStatement.setString(6, event_time);
            preparedStatement.setString(7, event_status);
            preparedStatement.setString(8, "0");
            preparedStatement.setString(9, "0");
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "提交成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "提交失败");

        return jo;
    }

    //提交不良事件属性列
    public static JSONObject insertEventDetailed(String form_code,String property_code,String detailed_data) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("insert into event_detailed(form_code,property_code,detailed_data) values(?,?,?)");
            preparedStatement.setString(1, form_code);
            preparedStatement.setString(2, property_code);
            preparedStatement.setString(3, detailed_data);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "提交成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "提交失败");

        return jo;
    }

    //修改不良事件
    public static JSONObject updateEventDetailed(String form_code,String property_code,String detailed_data) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event_detailed set detailed_data = ? where form_code = ? and property_code = ?");
            preparedStatement.setString(1, detailed_data);
            preparedStatement.setString(2, form_code);
            preparedStatement.setString(3, property_code);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "修改失败");

        return jo;
    }

    //修改事件提交状态
    public static JSONObject updateEventStatus(String status, String form_code) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            preparedStatement = conn.prepareStatement("update event_all set event_status = ? where form_code = ?");
            preparedStatement.setString(1, status);
            preparedStatement.setString(2, form_code);
            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "修改成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "修改失败");

        return jo;
    }

    //提交处理意见
    public static JSONObject handleEvent(String form_code,String suggestion,String reporter_dept) {
        Connection conn = DbUtil.getConn();
        System.out.println(conn);
        if (conn == null) return null;

        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        JSONObject jo = new JSONObject();

        try {
            if(reporter_dept.equals("医务科"))
            {
                preparedStatement = conn.prepareStatement(
                        "update event_all set med_dept_suggestion = ? , med_dept_status = ? where form_code = ?");
                preparedStatement.setString(1,suggestion);
                preparedStatement.setString(2,"1");
                preparedStatement.setString(3,form_code);
            }
            else {
                preparedStatement = conn.prepareStatement(
                        "update event_all set dept_suggestion = ? , dept_status = ? where form_code = ?");
                preparedStatement.setString(1,suggestion);
                preparedStatement.setString(2,"1");
                preparedStatement.setString(3,form_code);
            }

            if(preparedStatement.executeUpdate()!=0) {
                jo.set("code", 1000);
                jo.set("msg", "处理成功");
                return jo;
            }
        } catch (SQLException e) {
            try {
//                conn.rollback();
            } catch (Exception ee) {
                System.out.println(ee.getMessage());
            }

            System.out.println(e.getMessage());
        } finally {
            try {
                if (resultSet != null) resultSet.close();
                if (preparedStatement != null) preparedStatement.close();
                conn.close();
            } catch (SQLException e) {
                System.out.println(e.getMessage());
            }
        }

        jo.set("code", 1002);
        jo.set("msg", "提交失败");

        return jo;
    }

    public static void main(String[] args){
//        System.out.println(check("ys0403"));
//        String dept_name = "产科";
//        System.out.println(selectDeptData(" and event_all.reporter_dept = "+dept_name));
        System.out.println(handleSelect("产科","无","无"));
    }
}
